﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using Biblioteca.DataBase;
using Pms.Utils;

namespace SERIX.Classes
{
    public class CSociosOrg
    {
        private string _idt_socio;
        private string _idt_org;
        private string _socio;
        private string _cpf;

        public CSociosOrg()
        {

        }

        public string IdtSocio
        {
            get { return this._idt_socio; }
            set { this._idt_socio = value; }
        }

        public string IdtOrg
        {
            get { return this._idt_org; }
            set { this._idt_org = value; }
        }

        public string Socio
        {
            get { return this._socio; }
            set { this._socio = value; }
        }

        public string Cpf
        {
            get { return this._cpf; }
            set { this._cpf = value; }
        }

        public DataTable Tabela(string idt_org)
        {
            DataTable tb = new DataTable();

            tb.Columns.Add("IDT_SOCIO");
            tb.Columns.Add("IDT_ORG");
            tb.Columns.Add("SOCIO");
            tb.Columns.Add("CPF");

            OracleConnection conexao = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string sql = @"SELECT IDT_SOCIO, IDT_ORG, SOCIO, CPF FROM Serix.SRX_SOCIOS_ORG WHERE IDT_ORG = :IDT_ORG";
            OracleCommand cmd = new OracleCommand(sql, conexao);

            try
            {
                conexao.Open();
                cmd.Parameters.Add("IDT_ORG", OracleType.Number);
                cmd.Parameters["IDT_ORG"].Value = idt_org;

                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(tb);
               
            }
            catch(Exception e)
            {

            }
            finally { conexao.Close(); }


            return tb;
        }


        public void InserirSocio(string idt_socio, string idt_org, string socio, string cpf)
        {
            OracleConnection conection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string sql = @"INSERT INTO Serix.SRX_SOCIOS_ORG
                            (IDT_SOCIO, IDT_ORG, SOCIO, CPF)
                            VALUES
                            (:IDT_SOCIO, :IDT_ORG, :SOCIO, :CPF)";
            OracleCommand cmd = new OracleCommand(sql, conection);

            try
            {
                conection.Open();
                cmd.Parameters.Add("IDT_SOCIO", OracleType.Number);
                cmd.Parameters.Add("IDT_ORG", OracleType.Number);
                cmd.Parameters.Add("SOCIO", OracleType.VarChar);
                cmd.Parameters.Add("CPF", OracleType.Char);

                cmd.Parameters["IDT_SOCIO"].Value = idt_socio;
                cmd.Parameters["IDT_ORG"].Value = idt_org;
                cmd.Parameters["SOCIO"].Value = socio;
                cmd.Parameters["CPF"].Value = cpf;

                cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                conection.Close();
            }
            finally { conection.Close(); }
        }

        static public int ObterIdt(string login)
        {
            string sql = @"SELECT COUNT(IDT_SOCIO) FROM Serix.SRX_SOCIOS_ORG";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), sql);
                qy.Executar();
                return Convert.ToInt32(qy.ObterCampo("COUNT(IDT_SOCIO)"))+1;
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return -1;
            }
            finally
            {
                qy.FecharConexao();
            }
        }

      
    }
}
